package Dao.Grade.Imp;

import Dao.Grade.FinalGradeDao;
import Entity.Grade.FinalGrade;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class FinalGradeDaoImp implements FinalGradeDao {
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    @Override
    public Integer insetAdsGrade(FinalGrade finalGrade, Connection conn) {
        int result = 0;
        try {
            String sql = "insert into final_grade(sno,grade1, grade1_id) values (?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, finalGrade.getSno());
            ps.setInt(2, finalGrade.getGrade1());
            ps.setInt(3, finalGrade.getGrade1Id());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }

    @Override
    public Integer upDateGrade(FinalGrade finalGrade, Connection conn) {
        int result = 0;
        try {
            String sql = "update final_grade set grade2 = ? , grade2_id = ? where  sno = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, finalGrade.getGrade2());
            ps.setInt(2, finalGrade.getGrade2Id());
            ps.setString(3, finalGrade.getSno());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }

    @Override
    public List<FinalGrade> getAllFinalGradeByNO(String queryKey, Integer offset, Integer limit, Connection conn) {
        List<FinalGrade> finalGrades = new ArrayList<>();
        try {
            String sql = "select" +
                    " sno, grade1, grade1_id, grade2, grade2_id, grade3, grade3_id, final_grade, pass_state, opinion, is_pass " +
                    " from final_grade " +
                    " limit ?,? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,offset);
            ps.setInt(2,limit);
            rs = ps.executeQuery();
            while (rs.next()) {
                FinalGrade finalGrade = setFinalGrade(rs);
                finalGrades.add(finalGrade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return finalGrades;
    }

    /***
     *
     * @param SNO 学号
     */
    @Override
    public FinalGrade getFinalBySNO(String SNO, Connection conn) {
        FinalGrade finalGrade = null;
        try {
            String sql = "select" +
                    " sno, grade1, grade1_id, grade2, grade2_id, grade3, grade3_id, final_grade, pass_state, opinion, is_pass " +
                    " from final_grade " +
                    " where sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, SNO);
            rs = ps.executeQuery();
            while (rs.next()) {
                finalGrade = setFinalGrade(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return finalGrade;
    }

    @Override
    public Integer updateGroupGrade(FinalGrade finalGrade, Connection conn) {
        int result = 0;
        try {
            String sql = "update final_grade set grade3 = ? , grade3_id = ? , final_grade = ? where  sno = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, finalGrade.getGrade3());
            ps.setInt(2, finalGrade.getGrade3Id());
            ps.setDouble(3,finalGrade.getFinalGrade());
            ps.setString(4, finalGrade.getSno());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }


    public FinalGrade setFinalGrade(ResultSet rs) throws SQLException {
        String sno = rs.getString(1);
        Integer grade = rs.getInt(2);
        Integer grade1Id = rs.getInt(3);
        Integer grade2 = rs.getInt(4);
        Integer grade2Id = rs.getInt(5);
        Integer grade3 = rs.getInt(6);
        Integer grade3Id = rs.getInt(7);
        Double finalGrade1 = rs.getDouble(8);
        String passState = rs.getString(9);
        String opinion = rs.getString(10);
        String isPass = rs.getString(11);
        return new FinalGrade(sno, grade, grade1Id, grade2, grade2Id, grade3, grade3Id,
                finalGrade1, passState, opinion, isPass, null, null, null, null);
    }
}
